Strategy for Excel ↔ MicroStation DGN Data Exchange using Python

Created from Question to MicroStation Python Assistant

Use Excel as a controlled tabular exchange format and Python as the integration layer. The workbook should contain a stable identifier for every DGN element so that exports and later updates can be matched reliably.

Recommended workbook structure …

• Create separate worksheets according to the data being exchanged, for example:
• Worksheet Purpose
• Elements One row per DGN element: identifier, model, element type, level, geometry values, and editable business fields.
• Import Rows describing new elements to create in the active model.
• Validation Import/update results, rejected rows, and error messages.
• Lists Optional controlled value lists used by Excel validation.

Every exchange row should include at least:

• DgnFile or logical source name
• ModelName
• ElementId — persistent element identifier for existing elements
• ElementType
• Level
• Coordinate and dimension columns where applicable
• A status column such as New, Update, Skip, or Error

Use a unique external key as well when the organization has one, such as AssetCode. Do not rely on Excel row numbers as identifiers.

Import new Data from Excel to MicroStation

Process …

1. The user prepares rows on the Import worksheet.
2. Python reads the workbook, preferably with openpyxl.
3. Validate each row before creating any geometry:
   • Required fields are populated.
   • Coordinates and dimensions are numeric.
   • Element type is supported.
   • Level names exist or are approved for creation.
   • The external key is unique.
4. Convert Excel coordinate and dimension values from master units to UORs before geometry creation.
5. Create each supported element in the active DGN model.
6. Apply level, symbology, and the required element data.
7. Add each new element to the active model.
8. Write results back to the Validation worksheet, including the newly assigned ElementId.

Important design decisions

• Begin with a limited list of supported geometry, for example lines, linestrings, text, circles, or cells.
• Treat one row as one element unless a geometry definition explicitly requires multiple rows.
• Use an import preview mode first: validate all rows and report what would be created without modifying the model.
• Prefer an all-or-nothing batch policy where practical: if validation errors occur, do not create partial data.

Export data from a DGN model to an Excel workbook

Process …

1. Access the active DGN model.
2. Scan elements based on defined criteria:
   • Entire model, or
   • Specific levels, element classes, fences, or selection sets.
3. For each eligible element, extract:
   • Persistent element identifier
   • Model name
   • Element type
   • Level and symbology
   • Geometry information, such as start/end points, vertices, origin, radius, or text origin
   • Required associated data fields
4. Convert coordinates and dimensions from UORs to master units for Excel.
5. Write one normalized record per element to the Elements worksheet.
6. Format Excel output:
   • Header row
   • Table filters
   • Frozen panes
   • Number formats appropriate for coordinates and dimensions
   • Workbook metadata such as export timestamp and source model

Export principles

• Include the ElementId in every export row. It is essential for safe later updates.
• Export only fields intended for review or editing.
• Keep geometry and editable data in clearly named columns.
• Avoid exporting display-only values when a stable source value is available.
• Include a read-only ExportedAt timestamp to support audit and conflict checking.

Update existing DGN data from an Excel workbook

Matching strategy

Updates should match Excel rows to DGN elements in this order …

1. ElementId and ModelName
2. An approved external key, if the element identifier is unavailable
3. Never use spatial proximity or row order as the primary matching mechanism

Process …

1. Export the current model to a workbook.
2. Users edit only approved columns.
3. Python reads the edited workbook.
4. Validate each update row:
   • The row is marked Update.
   • The target model matches the active model.
   • The ElementId is valid and still resolves to an element.
   • The actual element type matches the workbook element type.
   • Edited values meet required data rules.
5. Detect conflicts:
   • Compare the workbook export timestamp or a stored revision value against the current element state.
   • Flag rows where the element changed after export.
6. Apply permitted changes only.
7. Rewrite the updated elements to the active model.
8. Record the outcome of every row in the Validation worksheet.

Safe update policy

Define editable columns explicitly. For example …

Column group	Typical policy
External identifier / description fields	Editable
Level	Editable only if validated
Symbology	Editable only if part of the workflow
Coordinates and dimensions	Editable only in a geometry-update workflow
ElementId, model name, element type	Read-only
Export timestamps and revision fields	Read-only

Do not allow Excel to change an element into a different geometry type. Treat that as a delete-and-create workflow requiring explicit approval.

Python implementation architecture. Use separate modules or functions for each responsibility …

excel_dgn_exchange/
├── workbook_io.py       # Read/write Excel worksheets
├── schema.py            # Column names and validation rules
├── dgn_reader.py        # Scan and extract DGN element data
├── dgn_writer.py        # Create and update DGN elements
├── validation.py        # Validate Excel and DGN records
├── report.py            # Write validation results to Excel
└── main.py              # User-facing import/export/update commands

Suggested Python libraries …

• OpenPyXL for .xlsx workbooks.
• MicroStation Python modules for model access, element scanning, geometry creation, and element updates.
• Standard Python library modules such as datetime, pathlib, csv where useful.

User Interface

Use tkinter or PyQt file dialogs to let the user select an input or output workbook.

Data Validation and Error Handling

Validation should occur before any DGN modification. Examples of errors to report in the terminal and in the workbook:

• Workbook or required worksheet does not exist.
• Required column is missing.
• Duplicate external key.
• Invalid ElementId.
• Element no longer exists in the model.
• Unsupported element type.
• Invalid coordinate, length, or radius.
• Level does not exist.
• Attempt to edit a protected/read-only column.
• Element has changed since the workbook was exported.

Each validation record should include …

Column	Example
RowNumber	18
Status	Error
Message	ElementId does not exist in the active model
ElementId	123456
Action	No changes made

Recommended Delivery Phases

Phase	Function	Comment
1	Export only	Export selected element types and basic attributes to Excel
2	Import new elements	Create a limited, well-defined set of geometry from the Import worksheet
3	Controlled updates	Update non-geometry fields using ElementId matching and validation
4	Geometry updates and conflict handling	Allow approved coordinate/dimension edits, revision checks, preview mode, and detailed audit reporting

This phased approach reduces risk and provides a reliable workbook format before enabling model modifications.

Questions

Post questions to the MicroStation Programming Forum.